import pymysql

class mysql_utils:
    def __init__(self, host, port, user_name, pass_word, db_name):
        self.host = host
        self.port = port
        self.user_name = user_name
        self.pass_word = pass_word
        self.db_name = db_name

        self.connect = pymysql.connect(host=self.host,
                               port=self.port,
                               user=self.user_name,
                               password=self.pass_word,
                               database=self.db_name,
                               charset='latin1')

    def get_connect(self):
        return self.connect
        """
        return pymysql.connect(host=self.host,
                               port=self.port,
                               user=self.user_name,
                               password=self.pass_word,
                               database=self.db_name,
                               charset='latin1')
        """

    def getLastId(self, db):
        db.execute("select last_insert_id();")
        data = db.fetchall();
        return data[0][0]

    def update(self,update_sql):
        conn = self.get_connect()
        cur = conn.cursor()
        result = 0
        try:

            result = cur.execute(update_sql)
            conn.commit()
        except:
            conn.rollback()
            raise
        finally:
            cur.close()
            conn.close()
        return result

    def update_deal_detail_status(self,status,dd_id):
        sql = "update tl_deal_detail set `status` = {} where id = {}".format(status,dd_id)
        conn = self.get_connect()
        cur = conn.cursor()
        result = 0
        try:

            result = cur.execute(sql)
            conn.commit()
        except:
            conn.rollback()
            raise
        finally:
            cur.close()
            conn.close()
        return result

    def batch_create(self,sql,data):
        conn = self.get_connect()
        cur = conn.cursor()
        result = 0
        try:

            result = cur.executemany(sql,data)
            conn.commit()
        except Exception as e:
            conn.rollback()
            print(e)
            raise
        finally:
            cur.close()
            conn.close()

        return result

    def create(self,create_sql):
        conn = self.get_connect()
        cur = conn.cursor()
        result = 0
        try:

            result = cur.execute(create_sql)
            if result >= 1:
                result = cur.lastrowid
            conn.commit()
        except:
            conn.rollback()
            raise
        finally:
            cur.close()
            conn.close()

        return result

    # def insert_deal_detail(self,_deal_detail):
    #     sql = "INSERT INTO tl_deal_detail (deal_strategy_id,spot_coin_name,future_coin_name," \
    #           "spot_exchange,future_exchange,guadan_position,per_deal_limit,future_contract_type,created_time,deal_profit) VALUES ({},'{}','{}','{}','{}',{},{},'{}',{},{})".format(_deal_detail.deal_strategy_id,
    #                                                                                                 _deal_detail.spot_coin_name,
    #                                                                                              _deal_detail.future_coin_name,_deal_detail.spot_exchange,
    #                                                                                              _deal_detail.future_exchange,_deal_detail.guadan_position,
    #                                                                                             _deal_detail.per_deal_limit,_deal_detail.future_contract_type,'now()',_deal_detail.deal_profit)
    #     conn = self.get_connect()
    #     cur = conn.cursor()
    #     result = 0
    #     try:
    #
    #         result = cur.execute(sql)
    #         if result >= 1:
    #             result = cur.lastrowid
    #         conn.commit()
    #     except:
    #         conn.rollback()
    #         raise
    #     finally:
    #         cur.close()
    #         conn.close()
    #
    #     return result
    #
    # def insert_deal_result(self,tl_deal_result):
    #     sql = "INSERT INTO tl_deal_result (strategy_id,symbol,funding_rate," \
    #           "funding_rate_time,open_price,close_price,open_time,close_time,profit,reason,created_time,updated_time,status,open_amount,open_order_id,stop_profit_order_id,stop_loss_order_id,position_side) VALUES ({},'{}',{},{},{},{},'{}','{}',{},{},{},{},{},{},'{}','{}','{}','{}')".format(tl_deal_result.strategy_id,
    #                                                                                                 tl_deal_result.symbol,
    #                                                                                              tl_deal_result.funding_rate,tl_deal_result.funding_rate_time,
    #                                                                                              tl_deal_result.open_price,tl_deal_result.close_price,
    #                                                                                             tl_deal_result.open_time,tl_deal_result.close_time,tl_deal_result.profit,tl_deal_result.reason,'now()','now()', tl_deal_result.status,tl_deal_result.open_amount,tl_deal_result.open_order_id,tl_deal_result.stop_profit_order_id,tl_deal_result.stop_loss_order_id,tl_deal_result.position_side)
    #
    #     conn = self.get_connect()
    #     cur = conn.cursor()
    #     result = 0
    #     try:
    #
    #         result = cur.execute(sql)
    #         if result >= 1:
    #             result = cur.lastrowid
    #         conn.commit()
    #     except:
    #         conn.rollback()
    #         raise
    #     finally:
    #         cur.close()
    #         conn.close()
    #
    #     return result

    def query(self, sql_str):
        conn = self.get_connect()
        cur = conn.cursor()
        cur.execute(sql_str)
        rows = cur.fetchall()
        cur.close()
        conn.close()
        return rows
